package com.fy.tt.dao.impl;

import com.fy.toolhelper.db.BaseDaoImpl;
import com.fy.tt.dao.ScriptDao;
import com.fy.tt.entity.Script;
import com.fy.tt.entity.Semantic;
import com.fy.tt.entity.Word;
import com.fy.tt.entity.query.ScriptQuery;
import com.fy.tt.entity.vo.ScriptVo;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ScriptDaoImpl extends BaseDaoImpl<Script> implements ScriptDao {

    @Override
    public List<ScriptVo> getAllScripts(Connection connection) throws SQLException {
        String sql = "select s.*,p.band_name from bls_script s left join bls_parser p on s.band_obj_id=p.band_obj_id ";

        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        ArrayList<ScriptVo> scripts = new ArrayList<>();
        while(rs.next()){
            ScriptVo script = new ScriptVo(rs);
            scripts.add(script);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return scripts;
    }


    @Override
    public int deleteScript(Connection connection, int id) throws SQLException {
        String sql = "delete from bls_script where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, id);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }


    @Override
    public List<Script> getPageScripts(Connection connection, int pageSize, int current) throws SQLException {
        int start = pageSize * (current - 1);
        String sql = "select * from bls_script limit ?,?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Script> scripts = new ArrayList<>();
        while(rs.next()){
            Script script = new Script(rs);
            scripts.add(script);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return scripts;
    }

    @Override
    public List<ScriptVo> getConditionPageScripts(Connection connection, ScriptQuery scriptQuery, int pageSize, int current) throws SQLException {
        int start = pageSize * (current - 1);
        String sql = "select s.*,p.band_name from bls_script s left join bls_parser p on s.band_obj_id=p.band_obj_id where 1 = 1";

        if(!StringUtils.isEmpty(scriptQuery.getName())){
            sql+=" and ( s.name like '%" + scriptQuery.getName() + "%'";
            sql+=" or s.content like '%" + scriptQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + scriptQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(scriptQuery.getType())){
            sql+=" and s.type = " + scriptQuery.getType();
        }
        if(!StringUtils.isEmpty(scriptQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + scriptQuery.getBandObjId();
        }

        if(StringUtils.isEmpty(scriptQuery.getName())
                && StringUtils.isEmpty(scriptQuery.getContent())
                && StringUtils.isEmpty(scriptQuery.getBandObjId())
                && StringUtils.isEmpty(scriptQuery.getType())){
            sql = "select s.*,p.band_name from bls_script s left join bls_parser p on s.band_obj_id=p.band_obj_id limit ? , ? ";
        }else{
            sql += " limit ? , ?";
        }

        System.out.println("sql = " + sql);

        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<ScriptVo> scripts = new ArrayList<>();
        while(rs.next()){
            ScriptVo script = new ScriptVo(rs);
            scripts.add(script);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return scripts;
    }

    @Override
    public int getTotalScript(Connection connection, ScriptQuery scriptQuery) throws SQLException {
        int count = 0;
        String sql = "select s.*,p.band_name from bls_script s left join bls_parser p on s.band_obj_id=p.band_obj_id where 1 = 1";
        if(!StringUtils.isEmpty(scriptQuery.getName())){
            sql+=" and ( s.name like '%" + scriptQuery.getName() + "%'";
            sql+=" or s.content like '%" + scriptQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + scriptQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(scriptQuery.getType())){
            sql+=" and s.type = " + scriptQuery.getType();
        }
        if(!StringUtils.isEmpty(scriptQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + scriptQuery.getBandObjId();
        }
        if(StringUtils.isEmpty(scriptQuery.getName())
                && StringUtils.isEmpty(scriptQuery.getContent())
                && StringUtils.isEmpty(scriptQuery.getBandObjId())
                && StringUtils.isEmpty(scriptQuery.getType())){
            sql = "select s.*,p.band_name from bls_script s left join bls_parser p on s.band_obj_id=p.band_obj_id";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        rs.last();
        count = rs.getRow();
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return count;
    }

      @Override
    public List<Word> getRelatedWord(Connection connection, int scriptId) throws SQLException {
        String sql = "select * from bls_word where id in (";
        String[] wordIds = getWordIds(connection, scriptId);
        for(int i = 0; i < wordIds.length; i++){
            if (i != wordIds.length - 1){
                sql += wordIds[i] + ",";
            }else{
                sql += wordIds[i] + ")";
            }
        }
        System.out.println("sql:"+sql);
        ArrayList<Word> words = new ArrayList<>();
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        while (rs.next()){
            Word word = new Word(rs);
            words.add(word);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return words;
    }

    @Override
    public List<Semantic> getRelatedSemantic(Connection connection, int scriptId) throws SQLException {
        String sql = "select * from bls_semantic where word_id in (";
        String[] wordIds = getWordIds(connection, scriptId);
        for(int i = 0; i < wordIds.length; i++){
            if (i != wordIds.length - 1){
                sql += wordIds[i] + ",";
            }else{
                sql += wordIds[i] + ")";
            }
        }
        System.out.println("sql:"+sql);
        ArrayList<Semantic> semantics = new ArrayList<>();
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        while (rs.next()){
            Semantic semantic = new Semantic(rs);
            semantics.add(semantic);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return semantics;
    }

    String[] getWordIds(Connection connection, int scriptId) throws SQLException {
        String relatedWords = "";
        String sql = "select related_words from bls_script where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1,scriptId);
        ResultSet rs = pstm.executeQuery();
        while(rs.next()){
            relatedWords = rs.getString("related_words");
        }
        String[] wordIds = relatedWords.split(";");
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return wordIds;
    }

    public ScriptDaoImpl() throws Exception {
    }
}
